/**
 * Copyright 2016-2017 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.inmorn.extspring.jdbc.mysql;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;

import com.inmorn.extspring.exception.DaoException;
import com.inmorn.extspring.metadata.IPage;
import com.inmorn.extspring.metadata.Page;
import com.inmorn.extspring.metadata.PageFooterColumn;
import com.inmorn.extspring.metadata.QueryPage;

public class BaseJdbcDao extends com.inmorn.extspring.jdbc.BaseJdbcDao{
	
	/**
	 *  MySql 分页查询
	 * @param sql
	 * @param args
	 * @param pageSize
	 * @param pageIndex
	 * @param rowMapper
	 * @return
	 * @throws DaoException
	 */
	@SuppressWarnings("rawtypes")
	public IPage<?> queryByPage(String sql, List<Object> args,QueryPage queryPage, RowMapper rowMapper) throws DaoException {
		return queryByPage(sql, args, queryPage.getPageSize(), queryPage.getPageIndex(), queryPage.getSortMap(), rowMapper);
	}
	
	/**
	 *  MySql 分页查询
	 * @param sql
	 * @param args
	 * @param pageSize
	 * @param pageIndex
	 * @param rowMapper
	 * @return
	 * @throws DaoException
	 */
	@SuppressWarnings("rawtypes")
	public IPage<?> queryByPage(String sql, List<Object> args, int pageSize,
			int pageIndex, final Map<String, String> sortMap, RowMapper rowMapper) throws DaoException {
		QueryPage queryPage = new QueryPage(pageSize, pageIndex);
		queryPage.setSqlString(sql);
		if (args != null) {
			for (Object arg : args) {
				queryPage.addQueryCondition(null, arg);
			}
		}
		queryPage.setRowMapper(rowMapper);
		queryPage.setSortMap(sortMap);
		return queryByPage(queryPage);
	}
	
	/**
	 *  MySql 分页查询
	 * @param sql
	 * @param args
	 * @param pageSize
	 * @param pageIndex
	 * @param rowMapper
	 * @return
	 * @throws DaoException
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public IPage<?> queryByPage(QueryPage queryPage)
			throws DaoException {
		
		String sql = queryPage.getSqlString();
		List args = queryPage.getAllNotNullArg();
		int pageSize = queryPage.getPageSize();
		int pageIndex = queryPage.getPageIndex();
		RowMapper rowMapper = queryPage.getRowMapper();
		boolean calCount = queryPage.isCalCount();
		Map<String, String> sortMap = queryPage.getSortMap();
		
		List<PageFooterColumn> footers = new ArrayList<PageFooterColumn>();
		List<Object> params = new ArrayList<Object>();
		
		if (args != null && args.size() > 0) {
			for (Object arg : args) {
				if ( (arg instanceof PageFooterColumn) )
					footers.add((PageFooterColumn) arg);
				else
					params.add(arg);
			}
		}
		
		// 汇总
		Map<String, Object> aggregations = null;
		Integer total = pageSize;
		if (calCount) {
			aggregations = aggregate(sql, params, footers);
			total = Integer.valueOf(aggregations.get(COUNT).toString());
		}

		// 如果有排序条件，在sqlString后添加order by
		StringBuffer sbSql = new StringBuffer(sql);
		if (sortMap != null && !sortMap.isEmpty()) {
			sbSql.append(ORDER_BY);
			int i = 0;
			for (Iterator it = sortMap.keySet().iterator(); it
					.hasNext();) {
				Object o = it.next();
				String fieldName = o.toString();
				String orderType = sortMap.get(o.toString())
						.toString();

				if (i > 0) {
					sbSql.append(COMMA);
				}

				if (ASC.equalsIgnoreCase(orderType)) {
					sbSql.append(fieldName)
							.append(SPACE).append(ASC);
				} else {
					sbSql.append(fieldName)
							.append(SPACE).append(DESC);
				}
				i++;
			}
			
		}

		// 翻页
		int newPageIndex = pageIndex;
		if (calCount) {
			if (pageIndex < 0) {
				newPageIndex = 0;
			}
			else if (total <= pageSize * (pageIndex)) {
				if (total > 0)
					newPageIndex = (total + pageSize - 1) / pageSize - 1;
			}
		}
		
		int firstResult = pageSize * (newPageIndex);
		params.add(firstResult); // FirstResult
		params.add(pageSize); // MaxResults

		Page page = new Page(
				getJdbcTemplate().query(sbSql+" limit ?,? ", params.toArray(), rowMapper), total,
				pageSize, newPageIndex);
		page.setUserdata(aggregations);
		
		return page;
	}
	
	/**
	 *  MySql 分页查询
	 * @param sql
	 * @param args
	 * @param pageSize
	 * @param pageIndex
	 * @param clazz 返回字段列表必须与实体类属性一致(WAYBILL_NO = waybillNo) 
	 * @return
	 * @throws DaoException
	 */
	public IPage<?> queryByPage(String sql, List<Object> args, 
			QueryPage queryPage,Class<?> clazz) throws DaoException {
		return queryByPage(sql, args, queryPage.getPageSize(), 
				queryPage.getPageIndex(), queryPage.getSortMap(),clazz);
	}
	
	/**
	 *  MySql 分页查询
	 * @param sql
	 * @param args
	 * @param pageSize
	 * @param pageIndex
	 * @param clazz 返回字段列表必须与实体类属性一致(WAYBILL_NO = waybillNo) 
	 * @return
	 * @throws DaoException
	 */
	public IPage<?> queryByPage(String sql, List<Object> args, int pageSize,
			int pageIndex, final Map<String, String> sortMap,Class<?> clazz) throws DaoException {
		QueryPage queryPage = new QueryPage(pageSize, pageIndex);
		queryPage.setSqlString(sql);
		if (args != null) {
			for (Object arg : args) {
				queryPage.addQueryCondition(null, arg);
			}
		}
		queryPage.setSortMap(sortMap);
		return queryByPage(queryPage,clazz);
	}
	
	/**
	 *  MySql 分页查询
	 * @param sql
	 * @param args
	 * @param pageSize
	 * @param pageIndex
	 * @param clazz
	 * @return
	 * @throws DaoException
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public IPage<?> queryByPage(QueryPage queryPage,Class<?> clazz)
			throws DaoException {
		
		String sql = queryPage.getSqlString();
		List args = queryPage.getAllNotNullArg();
		int pageSize = queryPage.getPageSize();
		int pageIndex = queryPage.getPageIndex();
		boolean calCount = queryPage.isCalCount();
		Map<String, String> sortMap = queryPage.getSortMap();
		
		List<PageFooterColumn> footers = new ArrayList<PageFooterColumn>();
		List<Object> params = new ArrayList<Object>();
		
		if (args != null && args.size() > 0) {
			for (Object arg : args) {
				if ( (arg instanceof PageFooterColumn) )
					footers.add((PageFooterColumn) arg);
				else
					params.add(arg);
			}
		}
		
		// 汇总
		Map<String, Object> aggregations = null;
		Integer total = pageSize;
		if (calCount) {
			aggregations = aggregate(sql, params, footers);
			total = Integer.valueOf(aggregations.get(COUNT).toString());
		}

		// 如果有排序条件，在sqlString后添加order by
		StringBuffer sbSql = new StringBuffer(sql);
		if (sortMap != null && !sortMap.isEmpty()) {
			sbSql.append(ORDER_BY);
			int i = 0;
			for (Iterator it = sortMap.keySet().iterator(); it
					.hasNext();) {
				Object o = it.next();
				String fieldName = o.toString();
				String orderType = sortMap.get(o.toString())
						.toString();

				if (i > 0) {
					sbSql.append(COMMA);
				}

				if (ASC.equalsIgnoreCase(orderType)) {
					sbSql.append(fieldName)
							.append(SPACE).append(ASC);
				} else {
					sbSql.append(fieldName)
							.append(SPACE).append(DESC);
				}
				i++;
			}
			
			sbSql.append(COMMA).append("sys_guid()");
		}

		// 翻页
		int newPageIndex = pageIndex;
		if (calCount) {
			if (pageIndex < 0) {
				newPageIndex = 0;
			}
			else if (total <= pageSize * (pageIndex)) {
				if (total > 0)
					newPageIndex = (total + pageSize - 1) / pageSize - 1;
			}
		}
		
		int firstResult = pageSize * (newPageIndex);
		params.add(firstResult); // FirstResult
		params.add(pageSize); // MaxResults

		Page page = new Page(
				getJdbcTemplate().query(sql+" limit ?,? "
						,params.toArray()
						,new BeanPropertyRowMapper(clazz))
				, total,
				pageSize, newPageIndex);
		page.setUserdata(aggregations);
		
		return page;
	}
	
	/**
	 *  MySql 分页查询
	 * @param sql
	 * @param args
	 * @param pageSize
	 * @param pageIndex
	 * @param rowMapper
	 * @return
	 * @throws DaoException
	 */
	public Map<String, Object> aggregate(String sql, List<Object> args, List<PageFooterColumn> footers) {
		StringBuilder aggSql = new StringBuilder();
		aggSql.append("select count(*) as ");
		aggSql.append(COUNT).append(SPACE);
		if (footers != null) {
			for (PageFooterColumn column : footers) {
				aggSql.append(",").append(column.getAggExpression()).append(" as ").append(column.getName());
			}
		}
		aggSql.append(" from (").append(sql).append(") t ");
		
		if(logger.isDebugEnabled())
			logger.debug("aggSql:" + aggSql.toString());
		Map<String, Object> map = this.getJdbcTemplate().queryForMap(aggSql.toString(), args.toArray());
		if (footers != null) {
			for (PageFooterColumn column : footers) {
				String key = column.getName().toUpperCase();
				Object value = map.get(key);
				map.remove(key);
				map.put(column.getName(), value);
			}
		}
		
		return map;
	}
}
